package com.css.bjdt_report.validation.service;

import java.util.List;

import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.css.bjdt_report.cell.entity.Cell;
import com.css.bjdt_report.report.entity.RptTemplateValidation;
import com.css.bjdt_report.report.entity.ViewRptGroupTemplate;
import com.css.bjdt_report.util.CommonUtil;

//修改数据区域之后，重新生成单张表的校核公式
@Service
public class ReportValidationFormulaOnloadService {

	@Autowired
	private Dao dao;

	//重新加载表内单元格的校核公式
	public void onloadValidationCellFormula(String templateId) {
		try {
			List<RptTemplateValidation> validationInfoList = dao.query(RptTemplateValidation.class,
					Cnd.where("TEMPLATE_ID", "=", templateId).and("VALIDATION_TYPE", "=", "3"));
			String strSql = "update RPT_TEMPLATE_VALIDATION set VALIDATION_FORMULA = @validationFormula where VALIDATION_ID = @validationId";
			Sql sql = Sqls.create(strSql);
			for (RptTemplateValidation rptTemplateValidation : validationInfoList) {
				String validationFormula = rptTemplateValidation.getValidationFormulaShow();
				String[] validationNum = validationFormula.split("[\\+\\-\\*\\=\\>\\</]");
				for (String cellCoordinate : validationNum) {
					if (cellCoordinate.length() == 0 || cellCoordinate.length() == 1) {
						continue;
					}
					String numStr = cellCoordinate.replaceAll("[a-zA-Z]", "");
					String letterStr = cellCoordinate.replaceAll("[0-9]", "");
					String letterNumStr = CommonUtil.letterToNum(letterStr);
					Cell cell = null;
					/*cell = dao.fetch(Cell.class, Cnd.where("TEMPLATE_ID", "=", templateId)
							.and("REL_ROW_NUM", "=", numStr).and("REL_COL_NUM", "=", letterNumStr));
					if (cell == null) {
						cell = dao.fetch(Cell.class, Cnd.where("TEMPLATE_ID", "=", templateId)
								.and("ABS_ROW_NUM", "=", numStr).and("ABS_COL_NUM", "=", letterNumStr));
					}*/
					cell = dao.fetch(Cell.class, Cnd.where("TEMPLATE_ID", "=", templateId)
							.and("ABS_ROW_NUM", "=", numStr).and("ABS_COL_NUM", "=", letterNumStr));
					if (cell == null) {
						continue;
					}
					validationFormula = validationFormula.replaceFirst(cellCoordinate, cell.getCellId());
				}
				//判断当前公式是否有=号
				if (!(validationFormula.contains(">") || validationFormula.contains("<"))) {
					validationFormula = validationFormula.replaceFirst("=", "==");
				}
				sql.params().set("validationFormula", validationFormula).set("validationId",
						rptTemplateValidation.getValidationId());
				dao.execute(sql);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	//重新加载表间校核公式
	public void onloadReportBetweenValidationFormual(String templateId, String annualId) {
		List<RptTemplateValidation> validationInfoList = dao.query(RptTemplateValidation.class,
				Cnd.where("TEMPLATE_ID", "=", templateId).and("VALIDATION_TYPE", "=", "4"));
		String strSql = "update RPT_TEMPLATE_VALIDATION set VALIDATION_FORMULA = @validationFormula where VALIDATION_ID = @validationId";
		Sql sql = Sqls.create(strSql);
		for (RptTemplateValidation rptTemplateValidation : validationInfoList) {
			//第一步先将公式拆成左右两个部分
			String validationFormula = rptTemplateValidation.getValidationFormulaShow();
			String[] validationNum = validationFormula.split("[\\=\\>\\<\\=>\\=</]");
			//第一步先将公式拆成左右两个部分
			String leftValidationFormula = validationNum[0];
			String rightValidationFormula = validationNum[1];
			try {
				if (rightValidationFormula.length() == 0 || rightValidationFormula.length() == 1) {
					rightValidationFormula = validationNum[2];
				} else {
					rightValidationFormula = validationNum[1];
				}
			} catch (Exception e) {
				System.out.println("------------------>" + validationFormula);
			}

			//先拆左边
			String[] leftValidationFormulaArray = leftValidationFormula.split("[\\+\\-\\*\\=\\>\\</]");
			for (String cellCoordinate : leftValidationFormulaArray) {
				String numStr = cellCoordinate.replaceAll("[a-zA-Z]", "");
				String letterStr = cellCoordinate.replaceAll("[0-9]", "");
				String letterNumStr = CommonUtil.letterToNum(letterStr);
				Cell cell = null;
				cell = dao.fetch(Cell.class, Cnd.where("TEMPLATE_ID", "=", templateId).and("REL_ROW_NUM", "=", numStr)
						.and("REL_COL_NUM", "=", letterNumStr));
				if (cell == null) {
					cell = dao.fetch(Cell.class, Cnd.where("TEMPLATE_ID", "=", templateId)
							.and("ABS_ROW_NUM", "=", numStr).and("ABS_COL_NUM", "=", letterNumStr));
				}
				validationFormula = validationFormula.replaceFirst(cellCoordinate, cell.getCellId());
			}
			//再拆右边
			String[] rightValidationFormulaArray = rightValidationFormula.split("[\\+\\-\\*\\=\\>\\</]");
			for (String cellString : rightValidationFormulaArray) {
				String[] cellStringArray = cellString.split("[\\./]");
				ViewRptGroupTemplate rptTemplate = dao.fetch(ViewRptGroupTemplate.class,
						Cnd.where("TEMPLATENAME", "=", cellStringArray[0]).and("ANNUALID", "=", annualId));
				String cellCoordinate = cellStringArray[1];
				String numStr = cellCoordinate.replaceAll("[a-zA-Z]", "");
				String letterStr = cellCoordinate.replaceAll("[0-9]", "");
				String letterNumStr = CommonUtil.letterToNum(letterStr);
				Cell cell = null;
				cell = dao.fetch(Cell.class, Cnd.where("TEMPLATE_ID", "=", rptTemplate.getTemplateId())
						.and("REL_ROW_NUM", "=", numStr).and("REL_COL_NUM", "=", letterNumStr));
				if (cell == null) {
					cell = dao.fetch(Cell.class, Cnd.where("TEMPLATE_ID", "=", rptTemplate.getTemplateId())
							.and("ABS_ROW_NUM", "=", numStr).and("ABS_COL_NUM", "=", letterNumStr));
				}
				if (cell == null) {
					System.out.println("--------------" + cellString + "------------------");
				}
				validationFormula = validationFormula.replaceFirst(cellString, cell.getCellId());
			}

			//判断当前公式是否有=号
			if (!(validationFormula.contains(">") || validationFormula.contains("<"))) {
				validationFormula = validationFormula.replaceFirst("=", "==");
			}
			sql.params().set("validationFormula", validationFormula).set("validationId",
					rptTemplateValidation.getValidationId());
			dao.execute(sql);
		}
	}
}
